﻿using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Windows;
using System.Diagnostics;

namespace PASS说明书挂接 {
	/// <summary>
	/// MainWindow.xaml 的交互逻辑
	/// </summary>
	public partial class MainWindow : Window {
		BackgroundWorker bw = new BackgroundWorker();
		OleDbConnection conn = new OleDbConnection();
		public MainWindow() {
			InitializeComponent();

			bw.WorkerSupportsCancellation = true;
			bw.WorkerReportsProgress = true;
			bw.DoWork += new DoWorkEventHandler(bw_DoWork);
			bw.ProgressChanged += new ProgressChangedEventHandler(bw_ProgressChanged);
			bw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bw_RunWorkerCompleted);
		}
		void bw_DoWork(object sender, DoWorkEventArgs e) {
			bw.ReportProgress(0, "检查结构");
			if (conn.State != ConnectionState.Open) {
				conn.Open();
			}
			var cmd = conn.CreateCommand();

			var tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
			if (tables.Select("[TABLE_NAME]='说明书挂接子表'").Length > 0) {
				cmd.CommandText = "drop table 说明书挂接子表";
				cmd.ExecuteNonQuery();
			}
			cmd.CommandText = @"create table 说明书挂接子表 (
							   mdid                 int                  not null,
							   piid                 int                  not null,
							   挂接类型              text(255)                  null
							)";
			//cmd.ExecuteNonQuery(); //由于每一步都有结果表，所以不用再单独生成结果表
			var schema = conn.GetSchema("Columns", new string[] { null, null, "上市产品基本信息" });
			var scriptBuilder = new List<string>();
			var task = new List<Rules>();
			#region 全等挂接
			task.Add(new Rules { Name = "全等挂接1", IsStorage = false, Field = new string[] { "genid", "st_formid", "strength", "st_compid" }, Preference = Preference.MaxId });
			task.Add(new Rules { Name = "全等挂接2", IsStorage = false, Field = new string[] { "genid", "剂型归组", "strength", "st_compid" }, Preference = Preference.MaxId });
			task.Add(new Rules { Name = "全等挂接3", IsStorage = false, Field = new string[] { "genid", "st_formid", "st_compid", "approvalno" }, Preference = Preference.MaxId });
			task.Add(new Rules { Name = "全等挂接4", IsStorage = false, Field = new string[] { "genid", "剂型归组", "st_compid", "approvalno" }, Preference = Preference.MaxId });
			task.Add(new Rules { Name = "全等挂接5", IsStorage = false, Field = new string[] { "genid", "st_formid", "st_compid", "cbrand_nameid" }, Preference = Preference.MaxId });
			task.Add(new Rules { Name = "全等挂接6", IsStorage = false, Field = new string[] { "genid", "剂型归组", "st_compid", "cbrand_nameid" }, Preference = Preference.MaxId });
			#endregion

			#region 参见挂接
			task.Add(new Rules { Name = "参见挂接1", IsStorage = true, Field = new string[] { "genid", "st_formid", "st_compid" }, Preference = Preference.No });
			task.Add(new Rules { Name = "参见挂接2", IsStorage = true, Field = new string[] { "genid", "剂型归组", "st_compid" }, Preference = Preference.No });
			task.Add(new Rules { Name = "参见挂接3", IsStorage = true, Field = new string[] { "genid", "st_formid", "cbrand_nameid" }, Preference = Preference.MaxId });
			task.Add(new Rules { Name = "参见挂接4", IsStorage = true, Field = new string[] { "genid", "剂型归组", "cbrand_nameid" }, Preference = Preference.MaxId });
			task.Add(new Rules { Name = "参见挂接5", IsStorage = false, Field = new string[] { "genid", "approvalno" }, Preference = Preference.MaxId });
			task.Add(new Rules { Name = "参见挂接6", IsStorage = false, Field = new string[] { "genid", "st_formid" }, Preference = Preference.ExistsDose });
			task.Add(new Rules { Name = "参见挂接7", IsStorage = false, Field = new string[] { "genid", "剂型归组" }, Preference = Preference.ExistsDose });
			task.Add(new Rules { Name = "参见挂接8", IsStorage = false, Field = new string[] { "参见组id", "st_formid", "st_compid" }, Preference = Preference.Strength });
			task.Add(new Rules { Name = "参见挂接9", IsStorage = false, Field = new string[] { "参见组id", "剂型归组", "st_compid" }, Preference = Preference.Strength });
			task.Add(new Rules { Name = "参见挂接10", IsStorage = false, Field = new string[] { "参见组id", "st_formid" }, Preference = Preference.ExistsDose });
			task.Add(new Rules { Name = "参见挂接11", IsStorage = false, Field = new string[] { "参见组id", "剂型归组" }, Preference = Preference.ExistsDose });
			task.Add(new Rules { Name = "参见挂接12", IsStorage = false, Field = new string[] { "参见组id", "剂型归类" }, Preference = Preference.MaxId });
			#endregion

			if (schema.Select("COLUMN_NAME='piid'").Length > 0) {
				scriptBuilder.Add("piid");
			}
			if (schema.Select("COLUMN_NAME='挂接类型'").Length > 0) {
				scriptBuilder.Add("挂接类型");
			}
			if (schema.Select("COLUMN_NAME='st_formid_说'").Length > 0) {
				scriptBuilder.Add("st_formid_说 ");
			}
			if (schema.Select("COLUMN_NAME='form_name_说'").Length > 0) {
				scriptBuilder.Add("form_name_说");
			}
			if (schema.Select("COLUMN_NAME='approvalno_说'").Length > 0) {
				scriptBuilder.Add("approvalno_说");
			}
			if (schema.Select("COLUMN_NAME='strength_说'").Length > 0) {
				scriptBuilder.Add("strength_说");
			}
			if (schema.Select("COLUMN_NAME='st_compid_说'").Length > 0) {
				scriptBuilder.Add("st_compid_说");
			}
			if (schema.Select("COLUMN_NAME='companycn_说'").Length > 0) {
				scriptBuilder.Add("companycn_说");
			}
			if (schema.Select("COLUMN_NAME='genid_说'").Length > 0) {
				scriptBuilder.Add("genid_说");
			}
			if (schema.Select("COLUMN_NAME='drgname_说'").Length > 0) {
				scriptBuilder.Add("drgname_说");
			}
			if (schema.Select("COLUMN_NAME='参见组id_说'").Length > 0) {
				scriptBuilder.Add("参见组id_说");
			}
			if (schema.Select("COLUMN_NAME='剂型归类_说'").Length > 0) {
				scriptBuilder.Add("剂型归类_说");
			}
			if (schema.Select("COLUMN_NAME='剂型归组_说'").Length > 0) {
				scriptBuilder.Add("剂型归组_说");
			}
			if (scriptBuilder.Count > 0) {
				cmd.CommandText = "ALTER TABLE 上市产品基本信息 DROP COLUMN " + string.Join(",", scriptBuilder);
				cmd.ExecuteNonQuery();
			}

			cmd.CommandText = "ALTER TABLE 上市产品基本信息 ADD COLUMN piid int,挂接类型 text(255)";
			//,st_formid_说 int,form_name_说 text(255),approvalno_说 text(255),strength_说 text(255),st_compid_说 int,companycn_说 text(255),genid_说 int,drgname_说 text(255),参见组id_说 int,剂型归类_说 text(255),剂型归组_说 text(255)
			cmd.ExecuteNonQuery();
			bw.ReportProgress(100);
			Stopwatch timeer = new Stopwatch();
			foreach (var item in task) {
				if (bw.CancellationPending) {
					e.Cancel = true;
					return;
				}
				bw.ReportProgress(0, item.Name);
				if (tables.Select(string.Format("[TABLE_NAME]='{0}'", item.Name)).Length > 0) {
					cmd.CommandText = string.Format("drop table {0}", item.Name);
					cmd.ExecuteNonQuery();
				}
				string joinSql = string.Empty;
				joinSql = string.Format("SELECT DISTINCT a.mdid,a.st_cname,a.genid,a.st_compid,a.st_comp_name,a.st_formid,a.st_form_name,a.strength,a.approvalno,a.cbrand_nameid,a.st_cbrand,a.参见组id,a.剂型归类,a.剂型归组, b.piid INTO [{0}] FROM 上市产品基本信息 as a INNER JOIN 说明书结果 as b ON {1} WHERE {2} AND a.piid Is Null", item.Name, string.Join(" AND ", item.Field.Select(field => string.Format("a.[{0}]=b.[{0}]", field))), string.Join(" AND ", item.Field.Select(field => string.Format("Not a.[{0}] Is Null", field))));
				cmd.CommandText = joinSql;
				ExecuteSql(cmd);
				cmd.CommandText = string.Format("CREATE INDEX index_{0}_1 on [{0}](mdid ASC,piid ASC)", item.Name);
				ExecuteSql(cmd);
				bw.ReportProgress(20, item.Name);
				cmd.CommandText = string.Format("SELECT mdid, First(a.piid) AS piid INTO [#TEMP] FROM [{0}] as a GROUP BY mdid HAVING (Count(piid)=1)", item.Name);
				ExecuteSql(cmd);
				bw.ReportProgress(25, item.Name);
				cmd.CommandText = string.Format("UPDATE 上市产品基本信息 as a INNER JOIN [#TEMP] as b ON a.mdid = b.mdid SET a.piid = b.[piid],a.挂接类型='{0}'", item.Name);
				ExecuteSql(cmd);
				bw.ReportProgress(30, item.Name);
				cmd.CommandText = "CREATE INDEX index_temp_2 on [#TEMP](mdid ASC,piid ASC)";
				ExecuteSql(cmd);
				cmd.CommandText = string.Format("DELETE FROM [{0}] AS a WHERE EXISTS(SELECT mdid FROM [#TEMP] WHERE mdid=a.mdid and piid=a.piid)", item.Name);
				ExecuteSql(cmd);
				bw.ReportProgress(35, item.Name);
				cmd.CommandText = "DROP TABLE [#TEMP]";
				ExecuteSql(cmd);

				if (item.Preference == Preference.MaxId || item.Preference == Preference.ExistsDose || item.Preference == Preference.Strength) {
					cmd.CommandText = string.Format("SELECT mdid, Max([{0}].piid) AS piid INTO [#TEMP] FROM [{0}] GROUP BY mdid", item.Name);
					ExecuteSql(cmd);
					bw.ReportProgress(50, item.Name);
					cmd.CommandText = string.Format("UPDATE 上市产品基本信息 as a INNER JOIN [#TEMP] as b ON a.mdid = b.mdid SET a.piid = b.[piid],a.挂接类型='{0}'", item.Name);
					ExecuteSql(cmd);
					bw.ReportProgress(75, item.Name);
					cmd.CommandText = "DROP TABLE [#TEMP]";
					ExecuteSql(cmd);
				}
				if (item.Preference == Preference.No) {
					cmd.CommandText = string.Format("UPDATE 上市产品基本信息 as a INNER JOIN [{0}] as b ON a.mdid = b.mdid SET a.piid = 0,a.挂接类型='{0}'", item.Name);
					ExecuteSql(cmd);
					bw.ReportProgress(100, item.Name);
				}
				if (item.Preference == Preference.ExistsDose) {
					cmd.CommandText = string.Format("SELECT mdid, First(a.piid) AS piid INTO [#TEMP] FROM [{0}] as a WHERE Exists (select piid from 品种信息表 where piid=a.piid) GROUP BY mdid HAVING Count(piid)=1", item.Name);
					ExecuteSql(cmd);
					bw.ReportProgress(80, item.Name);
					cmd.CommandText = string.Format("UPDATE 上市产品基本信息 as a INNER JOIN [#TEMP] as b ON a.mdid = b.mdid SET a.piid = b.[piid]", item.Name);
					ExecuteSql(cmd);
					bw.ReportProgress(90, item.Name);
					cmd.CommandText = "CREATE INDEX index_temp_2 on [#TEMP](mdid ASC,piid ASC)";
					ExecuteSql(cmd);
					cmd.CommandText = string.Format("DELETE FROM [{0}] AS A WHERE EXISTS(SELECT mdid FROM [#TEMP] WHERE mdid=A.mdid and piid=A.piid)", item.Name);
					ExecuteSql(cmd);
					cmd.CommandText = "DROP TABLE [#TEMP]";
					ExecuteSql(cmd);
				}
				if (item.Preference == Preference.Strength) {
					cmd.CommandText = string.Format("SELECT c.mdid AS mdid, First(c.piid) AS piid INTO [#TEMP] FROM (SELECT b.mdid, b.piid FROM (上市产品基本信息 AS a INNER JOIN {0} AS b ON a.mdid = b.mdid) INNER JOIN 说明书结果 as c ON (a.strength = c.strength) AND (b.piid = c.piid) WHERE Not a.strength Is Null) AS c GROUP BY mdid HAVING Count([piid])=1", item.Name);
					ExecuteSql(cmd);
					bw.ReportProgress(80, item.Name);
					cmd.CommandText = string.Format("UPDATE 上市产品基本信息 as a INNER JOIN [#TEMP] as b ON a.mdid = b.mdid SET a.piid = b.[piid]", item.Name);
					ExecuteSql(cmd);
					bw.ReportProgress(90, item.Name);
					cmd.CommandText = "CREATE INDEX index_temp_2 on [#TEMP](mdid ASC,piid ASC)";
					ExecuteSql(cmd);
					cmd.CommandText = string.Format("DELETE FROM [{0}] AS A WHERE EXISTS(SELECT mdid FROM [#TEMP] WHERE mdid=A.mdid and piid=A.piid)", item.Name);
					ExecuteSql(cmd);
					cmd.CommandText = "DROP TABLE [#TEMP]";
					ExecuteSql(cmd);
				}
				bw.ReportProgress(100, item.Name);
			}
		}

		public void ExecuteSql(OleDbCommand cmd) {
			Stopwatch timer = new Stopwatch();
			Debug.WriteLine(cmd.CommandText);
			timer.Start();
			cmd.ExecuteNonQuery();
			timer.Stop();
			Debug.WriteLine(timer.ElapsedMilliseconds);
		}

		void bw_ProgressChanged(object sender, ProgressChangedEventArgs e) {
			progressBar1.Value = e.ProgressPercentage;
			if (e.UserState != null) {
				label2.Content = e.UserState.ToString();
			}
		}

		void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) {
			if (e.Cancelled) {
				MessageBox.Show("用户取消了操作！", "操作取消");
			}
			if (e.Error != null) {
				MessageBox.Show("程序发生了异常！\n\n" + e.Error.Message, "错误");
			}
			if (!e.Cancelled && e.Error == null) {
				MessageBox.Show("挂接完成", "完成");
			}

			btnBrowser.IsEnabled = true;
			btnStart.IsEnabled = true;
			tbDatabase.IsEnabled = true;
			btnCancel.IsEnabled = false;
			conn.Close();
		}
		private void button2_Click(object sender, RoutedEventArgs e) {
			string cString = tbDatabase.Text;
			if (String.IsNullOrEmpty(cString)) {
				MessageBox.Show("请选择数据库文件！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
				return;
			}
			if (cString.IndexOfAny(new char[] { '/', '\\' }) == -1) {
				cString = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, cString);
			}
			if (!File.Exists(cString)) {
				MessageBox.Show("数据库文件不存在！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
				return;
			}

			OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();
			builder.Provider = "Microsoft.Jet.OLEDB.4.0";
			builder.DataSource = cString;
			conn.Close();
			conn.ConnectionString = builder.ConnectionString;
			try {
				conn.Open();
			}
			catch (Exception) {
				MessageBox.Show("不能打开数据库！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
				return;
			}
			if (conn.State != ConnectionState.Open) {
				MessageBox.Show("不能打开数据库！", "错误", MessageBoxButton.OK, MessageBoxImage.Error);
				return;
			}
			btnBrowser.IsEnabled = false;
			btnStart.IsEnabled = false;
			tbDatabase.IsEnabled = false;
			btnCancel.IsEnabled = true;
			bw.RunWorkerAsync();
		}

		private void button1_Click(object sender, RoutedEventArgs e) {
			OpenFileDialog file = new OpenFileDialog();
			file.Filter = "Access文件 (*.mdb)|*.mdb|All files (*.*)|*.*";
			file.CheckFileExists = true;
			file.CheckPathExists = true;
			file.Title = "请选择要进行说明书挂接的数据库文件";
			file.RestoreDirectory = true;
			if (file.ShowDialog() == true) {
				tbDatabase.Text = file.FileName;
			}
		}
	}

	public class Rules {
		public string Name { get; set; }
		public string[] Field { get; set; }
		public Preference Preference { get; set; }
		public bool IsStorage { get; set; }
	}
	public enum Preference {
		/// <summary>
		/// 不选择
		/// </summary>
		No,
		/// <summary>
		/// 最大piid
		/// </summary>
		MaxId,
		/// <summary>
		/// 存在剂量表中的优先
		/// </summary>
		ExistsDose,
		/// <summary>
		/// 规格相同优先
		/// </summary>
		Strength
	}
}
